﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace HttpRequest.Helper
{
    public class SQLiteHelper
    {
        private static SQLiteConnection connection = null;
        // 指定数据库文件路径
        private string dbFilePath = "app.db";

        /// <summary>
        /// 创建sqlite连接
        /// </summary>
        /// <returns></returns>
        private SQLiteConnection getConn()
        {
            if (connection == null)
            {
                // 连接字符串
                string connectionString = $"Data Source={dbFilePath};Version=3;";
                connection = new SQLiteConnection(connectionString);
                connection.Open();
                return connection;
            }
            else
            {
                if (connection.State == System.Data.ConnectionState.Open)
                {
                    return connection;

                }
                else
                {
                    connection.Open();
                    return connection;
                }
            }
        }

        /// <summary>
        /// 创建表
        /// "CREATE TABLE IF NOT EXISTS Users (Id INTEGER PRIMARY KEY AUTOINCREMENT, Name TEXT, Age INTEGER);"
        /// </summary>
        /// <param name="createSql"></param>
        /// <returns></returns>
        public int CreateTable(string createSql)
        {
            SQLiteConnection connection = getConn();

            using (SQLiteCommand command = new SQLiteCommand(createSql, connection))
            {
                var result = command.ExecuteNonQuery();
                return result;
            }
        }

        /// <summary>
        /// 插入数据
        /// "INSERT INTO Users (Name, Age) VALUES (@Name, @Age);"
        /// </summary>
        /// <param name="insertSql"></param>
        /// <returns></returns>
        public int InsertData(string insertSql)
        {
            SQLiteConnection connection = getConn();

            using (SQLiteCommand command = new SQLiteCommand(insertSql, connection))
            {
                //command.Parameters.AddWithValue("@Name", name);
                //command.Parameters.AddWithValue("@Age", age);
                var insertResult = command.ExecuteNonQuery();
                Console.WriteLine("Data inserted.");
                return insertResult;
            }
        }

        /// <summary>
        /// 查询DataTable
        /// "SELECT * FROM Users;"
        /// </summary>
        /// <param name="querySql"></param>
        /// <returns></returns>
        public DataTable QueryDataTable(string querySql)
        {
            SQLiteConnection connection = getConn();

            DataSet ds = new DataSet();
            using (SQLiteCommand command = new SQLiteCommand(querySql, connection))
            {
                using (SQLiteDataAdapter adapter = new SQLiteDataAdapter(command))
                {
                    adapter.Fill(ds);
                    return ds.Tables[0];
                }
            }
        }

        /// <summary>
        /// 更新数据
        /// "UPDATE Users SET Name=@Name, Age=@Age WHERE Id=@Id;"
        /// </summary>
        /// <param name="updateSql"></param>
        /// <returns></returns>
        public int UpdateData(string updateSql)
        {
            SQLiteConnection connection = getConn();

            using (SQLiteCommand command = new SQLiteCommand(updateSql, connection))
            {
                return command.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// 删除数据
        /// "DELETE FROM Users WHERE Id=@Id;"
        /// </summary>
        /// <param name="DeleteSql"></param>
        /// <returns></returns>
        public int DeleteData(string DeleteSql)
        {
            SQLiteConnection connection = getConn();

            using (SQLiteCommand command = new SQLiteCommand(DeleteSql, connection))
            {
                return command.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// 查询数据返回Scalar
        /// select count(1) from users;
        /// </summary>
        /// <param name="querySql"></param>
        /// <returns></returns>
        public object QueryDataScalar(string querySql)
        {
            SQLiteConnection connection = getConn();
            using(SQLiteCommand command= new SQLiteCommand(querySql, connection))
            { return command.ExecuteScalar(); }
        }

        /// <summary>
        /// 查询数据返回Reader
        /// "SELECT * FROM Users;"
        /// </summary>
        /// <param name="querySql"></param>
        /// <returns></returns>
        public SQLiteDataReader QueryDataReader(string querySql)
        {
            SQLiteConnection connection = getConn();
            using (SQLiteCommand command = new SQLiteCommand(querySql, connection))
            {
                SQLiteDataReader reader = command.ExecuteReader();
                return reader;
            }
        }
    }
}
